﻿using System.Data.SqlClient;
using System.Xml;
using CompanyDBInterface;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using System;
using System.Linq;
using System.Reflection;
using System.IO;

namespace CompanyCRUDTests
{


    /// <summary>
    ///This is a test class for CompanyDALTest and is intended
    ///to contain all CompanyDALTest Unit Tests
    ///</summary>
    [TestClass()]
    public class CompanyDALTest
    {
        public const string PRODUCTION_DB_NAME = "CompanyDB";
        public const string TEST_DB_NAME = "CompanyDBTest";
        public const string CLONE_CONNECTION_STRING =
            @"Data Source=.\SQLEXPRESS;Initial Catalog=master;Integrated Security=True;MultipleActiveResultSets=True";

        private static string databaseDirectory;

        [ClassInitialize]
        public static void InitTestDatabase(TestContext testContext)
        {
            databaseDirectory = testContext.TestDeploymentDir;

            //CloneDatabase();

            //PopulateTestingData();

            XmlDocument xmlDoc = new XmlDocument();
            Assembly currentAssembly = Assembly.GetExecutingAssembly();
            Stream resourceStream = currentAssembly.GetManifestResourceStream("CompanyCRUDTests.people.xml");
            xmlDoc.Load(resourceStream);
        }

        private static void CloneDatabase()
        {
            // Clone the production database over the testing database
            string sqlCloneProductionDB = String.Format(@"
				USE master

				BACKUP DATABASE {0} TO DISK='C:\Temp\{0}.bak'

				IF EXISTS(SELECT * FROM sys.sysdatabases where name='{2}')
				BEGIN
					ALTER DATABASE {2} SET SINGLE_USER WITH ROLLBACK IMMEDIATE
					DROP DATABASE {2}
				END

				RESTORE DATABASE {2} FROM DISK='C:\Temp\{0}.bak'
				WITH
				MOVE '{0}' TO 'C:\Temp\{2}.mdf',
				MOVE '{0}_log' TO 'C:\Temp\{2}_Log.ldf'
			", PRODUCTION_DB_NAME, databaseDirectory, TEST_DB_NAME);

            SqlConnection dbConnection =
                new SqlConnection(CLONE_CONNECTION_STRING);
            dbConnection.Open();
            using (dbConnection)
            {
                SqlCommand cloneDBCmd = new SqlCommand(
                    sqlCloneProductionDB, dbConnection);
                cloneDBCmd.ExecuteNonQuery();
            }
        }

        [ClassCleanup]
        public static void CleanupTestDatabase()
        {
            // Drop the testing database
            CompanyDBEntities dbContext = new CompanyDBEntities();
            string sqlDropTestDatabase = String.Format(@"
				USE master

				IF EXISTS(SELECT * FROM sys.sysdatabases where name='{0}')
				BEGIN
					ALTER DATABASE {0} SET SINGLE_USER WITH ROLLBACK IMMEDIATE
					DROP DATABASE {0}
				END
			", TEST_DB_NAME);
            dbContext.ExecuteStoreCommand(sqlDropTestDatabase);
        }

        private static void PopulateTestingData()
        {
            CompanyDBEntities dbContext = new CompanyDBEntities();
            Country country = new Country();
            country.Name = "TestBulgaria";
            country.Language = "TestBulgarian";
            dbContext.Countries.AddObject(country);
            dbContext.SaveChanges();

            Company company = new Company()
            {
                Name = "TestCompany",
                Industry = "TestIndustry",
                CountryID = 7
            };
            dbContext.Companies.AddObject(company);
            dbContext.SaveChanges();

            Person testPerson1 = new Person()
            {
                FirstName = "testDimitar",
                LastName = "testDimitrov",
                Phone = "468974",
                CountryID = 7
            };
            dbContext.People.AddObject(testPerson1);
            dbContext.SaveChanges();

            Person testPerson2 = new Person()
            {
                FirstName = "testIliqn",
                LastName = "testIliev",
                Phone = "123456",
                CountryID = 7
            };
            dbContext.People.AddObject(testPerson2);
            dbContext.SaveChanges();

            Employee employee = new Employee()
            {
                HireDate = DateTime.Now,
                Salary = 400,
                PersonID = 25,
                CompanyID = 10
            };
            dbContext.Employees.AddObject(employee);
            dbContext.SaveChanges();

            CompaniesCustomer companyCustomer = new CompaniesCustomer()
            {
                CompanyID = 10,
                PersonID = 25
            };
            dbContext.CompaniesCustomers.AddObject(companyCustomer);
            dbContext.SaveChanges();

            companyCustomer = new CompaniesCustomer()
            {
                CompanyID = 10,
                PersonID = 26
            };
            dbContext.CompaniesCustomers.AddObject(companyCustomer);
            dbContext.SaveChanges();
        }
        #region CREATE TESTS
        [TestMethod()]
        [DeploymentItem("CompanyDBInterface.exe")]
        public void AddCompanyTest()
        {
            CompanyDBEntities context = new CompanyDBEntities();
            Company newCompany = new Company()
            {
                Name = "TestCompanyName",
                Industry = "Test Industry",
                CountryID = 7
            };
            context.Companies.AddObject(newCompany);
            context.SaveChanges();

            CompanyDBEntities anotherContext = new CompanyDBEntities();
            Company companyFromDB = (from c in anotherContext.Companies
                                     where c.ID == newCompany.ID
                                     select c).SingleOrDefault();
            Assert.AreEqual(companyFromDB.ID, newCompany.ID);
            Assert.AreEqual(companyFromDB.Name, newCompany.Name);
            Assert.AreEqual(companyFromDB.Industry, newCompany.Industry);
            Assert.AreEqual(companyFromDB.CountryID, newCompany.CountryID);
        }

        /// <summary>
        ///A test for AddCountry
        ///</summary>
        [TestMethod()]
        [DeploymentItem("CompanyDBInterface.exe")]
        public void AddCountryTest()
        {
            CompanyDBEntities context = new CompanyDBEntities();
            Country newCountry = new Country()
            {
                Name = "TestCountry",
                Language = "Bulgaristen"
            };
            context.Countries.AddObject(newCountry);
            context.SaveChanges();

            CompanyDBEntities anotherContext = new CompanyDBEntities();
            Country countryFromDB = (from c in anotherContext.Countries
                                     where c.ID == newCountry.ID
                                     select c).SingleOrDefault();
            Assert.AreEqual(countryFromDB.ID, newCountry.ID);
            Assert.AreEqual(countryFromDB.Name, newCountry.Name);
            Assert.AreEqual(countryFromDB.Language, newCountry.Language);
        }

        /// <summary>
        ///A test for AddCustomer
        ///</summary>
        [TestMethod()]
        [DeploymentItem("CompanyDBInterface.exe")]
        public void AddPersonTest()
        {
            CompanyDBEntities context = new CompanyDBEntities();
            Person newPerson = new Person()
            {
                FirstName = "TestFirstName",
                LastName = "TestLastName",
                Phone = "123456",
                CountryID = 7
            };
            context.People.AddObject(newPerson);
            context.SaveChanges();

            CompanyDBEntities anotherContext = new CompanyDBEntities();
            Person personFromDB = (from c in anotherContext.People
                                   where c.ID == newPerson.ID
                                   select c).SingleOrDefault();
            Assert.AreEqual(personFromDB.ID, newPerson.ID);
            Assert.AreEqual(personFromDB.FirstName, newPerson.FirstName);
            Assert.AreEqual(personFromDB.LastName, newPerson.LastName);
            Assert.AreEqual(personFromDB.Phone, newPerson.Phone);
            Assert.AreEqual(personFromDB.CountryID, newPerson.CountryID);
        }

        /// <summary>
        ///A test for AddEmployee
        ///</summary>
        [TestMethod()]
        [DeploymentItem("CompanyDBInterface.exe")]
        public void AddEmployeeTest()
        {
            CompanyDBEntities context = new CompanyDBEntities();
            Employee newEmployee = new Employee()
            {
                HireDate = DateTime.Now,
                Salary = 500,
                PersonID = 24,
                CompanyID = 10
            };
            context.Employees.AddObject(newEmployee);
            context.SaveChanges();

            CompanyDBEntities anotherContext = new CompanyDBEntities();
            Employee employeeFromDB = (from c in anotherContext.Employees
                                       where c.ID == newEmployee.ID
                                       select c).SingleOrDefault();
            Assert.AreEqual(employeeFromDB.ID, newEmployee.ID);
            //Assert.AreEqual(personFromDB.HireDate, newPerson.HireDate);
            Assert.AreEqual(employeeFromDB.Salary, newEmployee.Salary);
            Assert.AreEqual(employeeFromDB.PersonID, newEmployee.PersonID);
            Assert.AreEqual(employeeFromDB.CompanyID, newEmployee.CompanyID);
        }

        /// <summary>
        ///A test for AddPerson
        ///</summary>
        [TestMethod()]
        [DeploymentItem("CompanyDBInterface.exe")]
        public void AddCustomerTest()
        {
            CompanyDBEntities context = new CompanyDBEntities();
            CompaniesCustomer companyCustomer = new CompaniesCustomer()
            {
                PersonID = 24,
                CompanyID = 10
            };
            context.CompaniesCustomers.AddObject(companyCustomer);
            context.SaveChanges();

            CompanyDBEntities anotherContext = new CompanyDBEntities();
            CompaniesCustomer companyCustomerFromDB = (from c in anotherContext.CompaniesCustomers
                                                       where c.ID == companyCustomer.ID
                                                       select c).SingleOrDefault();
            Assert.AreEqual(companyCustomerFromDB.ID, companyCustomer.ID);
            Assert.AreEqual(companyCustomerFromDB.PersonID, companyCustomer.PersonID);
            Assert.AreEqual(companyCustomerFromDB.CompanyID, companyCustomer.CompanyID);
        }
        #endregion

        #region Delete Tests
        /// <summary>
        ///A test for DeleteCompany
        ///</summary>
        [TestMethod()]
        [DeploymentItem("CompanyDBInterface.exe")]
        public void DeleteCompanyTest()
        {
            CompanyDBEntities context = new CompanyDBEntities();
            Company newCompany = new Company()
            {
                Name = "TestCompanyName",
                Industry = "Test Industry",
                CountryID = 7
            };
            using (context)
            {
                context.Companies.AddObject(newCompany);
                context.SaveChanges();
            }

            CompanyDBEntities anotherContext = new CompanyDBEntities();
            using (anotherContext)
            {
                anotherContext.Companies.Attach(newCompany);
                anotherContext.Companies.DeleteObject(newCompany);
                anotherContext.SaveChanges();
            }

            CompanyDBEntities searchContext = new CompanyDBEntities();
            IQueryable<Company> isFoundCompany = from c in searchContext.Companies
                                 where c.ID == newCompany.ID
                                 select c;

            Assert.IsTrue(!isFoundCompany.Any());
        }

        [TestMethod()]
        [DeploymentItem("CompanyDBInterface.exe")]
        public void DeleteCompanyCustomerTest()
        {
            CompanyDBEntities context = new CompanyDBEntities();
            CompaniesCustomer companyCustomer = new CompaniesCustomer()
            {
                PersonID = 18,
                CompanyID = 6
            };
            using (context)
            {
                context.CompaniesCustomers.AddObject(companyCustomer);
                context.SaveChanges();
            }            

            CompanyDBEntities anotherContext = new CompanyDBEntities();
            using (anotherContext)
            {
                anotherContext.CompaniesCustomers.Attach(companyCustomer);
                anotherContext.CompaniesCustomers.DeleteObject(companyCustomer);
                anotherContext.SaveChanges();
            }

            CompanyDBEntities searchContext = new CompanyDBEntities();
            var isFoundCountry = from c in searchContext.CompaniesCustomers
                                 where c.ID == companyCustomer.ID
                                 select c;

            Assert.IsTrue(!isFoundCountry.Any());
        }

        /// <summary>
        ///A test for DeleteCountry
        ///</summary>
        [TestMethod()]
        [DeploymentItem("CompanyDBInterface.exe")]
        public void DeleteCountryTest()
        {
            CompanyDBEntities context = new CompanyDBEntities();
            Country newCountry = new Country()
            {
                Name = "TestCountry",
                Language = "Bulgaristen"
            };
            using (context)
            {
                context.Countries.AddObject(newCountry);
                context.SaveChanges();
            }            

            CompanyDBEntities anotherContext = new CompanyDBEntities();
            using (anotherContext)
            {
                anotherContext.Countries.Attach(newCountry);
                anotherContext.Countries.DeleteObject(newCountry);
                anotherContext.SaveChanges();
            }

            CompanyDBEntities searchContext = new CompanyDBEntities();
            var isFoundCountry = from c in searchContext.Countries
                                 where c.ID == newCountry.ID
                                                 select c;

            Assert.IsTrue(!isFoundCountry.Any());
        }

        /// <summary>
        ///A test for DeleteEmployee
        ///</summary>
        [TestMethod()]
        [DeploymentItem("CompanyDBInterface.exe")]
        public void DeleteEmployeeTest()
        {
            CompanyDBEntities context = new CompanyDBEntities();
            Employee newEmployee = new Employee()
            {
                HireDate = DateTime.Now,
                Salary = 500,
                PersonID = 24,
                CompanyID = 10
            };
            using (context)
            {
                context.Employees.AddObject(newEmployee);
                context.SaveChanges();
            }            

            CompanyDBEntities anotherContext = new CompanyDBEntities();
            using (anotherContext)
            {
                anotherContext.Employees.Attach(newEmployee);
                anotherContext.Employees.DeleteObject(newEmployee);
                anotherContext.SaveChanges();
            }

            CompanyDBEntities searchContext = new CompanyDBEntities();
            var isFoundEmployee = from c in searchContext.Employees
                                 where c.ID == newEmployee.ID
                                 select c;

            Assert.IsTrue(!isFoundEmployee.Any());
        }

        /// <summary>
        ///A test for DeletePerson
        ///</summary>
        [TestMethod()]
        [DeploymentItem("CompanyDBInterface.exe")]
        public void DeletePersonTest()
        {
            CompanyDBEntities context = new CompanyDBEntities();
            Person newPerson = new Person()
            {
                FirstName = "TestFirstName",
                LastName = "TestLastName",
                Phone = "123456",
                CountryID = 7
            };
            using (context)
            {
                context.People.AddObject(newPerson);
                context.SaveChanges();
            }

            CompanyDBEntities anotherContext = new CompanyDBEntities();
            using (anotherContext)
            {
                anotherContext.People.Attach(newPerson);
                anotherContext.People.DeleteObject(newPerson);
                anotherContext.SaveChanges();
            }

            CompanyDBEntities searchContext = new CompanyDBEntities();
            var isFoundPerson = from c in searchContext.Employees
                                  where c.ID == newPerson.ID
                                  select c;

            Assert.IsTrue(!isFoundPerson.Any());
        }
#endregion

        ///// <summary>
        /////A test for EditCompany
        /////</summary>
        //[TestMethod()]
        //[DeploymentItem("CompanyDBInterface.exe")]
        //public void EditCompanyTest()
        //{
        //    CompanyDAL_Accessor target = new CompanyDAL_Accessor(); // TODO: Initialize to an appropriate value
        //    Company company = null; // TODO: Initialize to an appropriate value
        //    target.EditCompany(company);
        //    Assert.Inconclusive("A method that does not return a value cannot be verified.");
        //}

        ///// <summary>
        /////A test for EditCompanyCustomer
        /////</summary>
        //[TestMethod()]
        //[DeploymentItem("CompanyDBInterface.exe")]
        //public void EditCompanyCustomerTest()
        //{
        //    CompanyDAL_Accessor target = new CompanyDAL_Accessor(); // TODO: Initialize to an appropriate value
        //    CompaniesCustomer companyCustomer = null; // TODO: Initialize to an appropriate value
        //    target.EditCompanyCustomer(companyCustomer);
        //    Assert.Inconclusive("A method that does not return a value cannot be verified.");
        //}

        ///// <summary>
        /////A test for EditCountry
        /////</summary>
        //[TestMethod()]
        //[DeploymentItem("CompanyDBInterface.exe")]
        //public void EditCountryTest()
        //{
        //    CompanyDAL_Accessor target = new CompanyDAL_Accessor(); // TODO: Initialize to an appropriate value
        //    Country country = null; // TODO: Initialize to an appropriate value
        //    target.EditCountry(country);
        //    Assert.Inconclusive("A method that does not return a value cannot be verified.");
        //}

        ///// <summary>
        /////A test for EditEmployee
        /////</summary>
        //[TestMethod()]
        //[DeploymentItem("CompanyDBInterface.exe")]
        //public void EditEmployeeTest()
        //{
        //    CompanyDAL_Accessor target = new CompanyDAL_Accessor(); // TODO: Initialize to an appropriate value
        //    Employee emp = null; // TODO: Initialize to an appropriate value
        //    target.EditEmployee(emp);
        //    Assert.Inconclusive("A method that does not return a value cannot be verified.");
        //}

        ///// <summary>
        /////A test for EditPerson
        /////</summary>
        //[TestMethod()]
        //[DeploymentItem("CompanyDBInterface.exe")]
        //public void EditPersonTest()
        //{
        //    CompanyDAL_Accessor target = new CompanyDAL_Accessor(); // TODO: Initialize to an appropriate value
        //    Person person = null; // TODO: Initialize to an appropriate value
        //    target.EditPerson(person);
        //    Assert.Inconclusive("A method that does not return a value cannot be verified.");
        //}
    }
}
